FrontPage/KexiDBDesign/KexiDBDrivers/

SQLite: Kexi Built-in DB Engine

Table of Contents    1. Ideas, recommendations    2. DB schema storage when no remote server is available    3. What's new (and interesting for Kexi Project)      3.1. SQLite3    4. Changes made to SQLite      4.1. All SQLite versions

We are using SQLite (http://www.sqlite.org) as default embedded engine for Kexi. SQLite source code is included within Kexi source code tree (in SVN repository: kexi/3rdparty/kexisql and kexi/3rdparty/kexisql3). Thus, no additional installartion efforts is needed to start use Kexi.

1. Ideas, recommendations

• It is recommended to look at parser from sqlite, and while we will extend this engine, it is also good to see e.g. at Postgresql sql parser's bison/flex sources (BSD License).

2. DB schema storage when no remote server is available

published: 2003-09-15 by js

Currently database schema is stored in database using special auto-created tables that is considered as system: kexiobjects, kexifields (there can be more).

In a situation when user cannot connect to network/remote database (or do not wants to do it), there is a requirement to offer him any local storage methods. In previous kexiDB release that was a storage from koffice libs, xml-based. Having effectife file-based driver that is always installed with Kexi (it is free of charge), we can just store DB schema in a file this database.

Notes:

• migration to other databases is added

3. What's new (and interesting for Kexi Project)

3.1. SQLite3

 2004-06-17 by js

• sqlite3_progress_handler() function: This routine configures a callback function - the progress callback - that is invoked periodically during long running calls to sqlite3_exec(), sqlite3_step() and sqlite3_get_table(). An example use for this API is to keep a GUI updated during a large query.
• Data types - more compact storage for integers and float numbers. "Strict affinity mode" what means strict type enforcements.

 2006-09-05 by js (SQLite > 3.3.6)

• use Virtual Tables to get external virtual data like CSV live queries, insteresting as we plan to add "cross-engine DB queries", similar idea.
• Use sqlite3_set_authorizer() to get fine-grained access control support
• Use SQLite's ability to load new SQL functions and collating sequences from shared libraries and DLLs

4. Changes made to SQLite

Since we have SQLite source ocde included in Kexi's source code tree, we were able to tweak it for our purposes. SQLite source distribution is powerfull enough, designed to be included in other projects (also for embedded systems), so for now there's no need for heavy tweaking.

Anyway, we're keeping all changes made to SQLite in this document: this will help when updating to newer SQLite versions will be performed.

4.1. All SQLite versions

• src/config.h file is renamed to sqliteconfig.h to avoid problems: KDE build tools, notably svn2dist script considers any config.h to be generated and thus it would be removed before building tarbal. This is especially dangerous as in this case, invalid configuration of sqlite will be produced, and compiled binaries will crash because of "btree.c:1043: sqlite3BtreeOpen: assertion sizeof(ptr)==sizeof(char*)".